﻿using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace AMS.Inventory.PDA.Castle.DataAccess
{
    public class SqliteDBServer
    {
        private SQLiteConnection _sqliteCon;
        public string DataSouce = "Data Source="+ModCommon.DBName;
        public SQLiteCommand SqliteCom;
        public SQLiteDataAdapter SqliteDa;

        public SqliteDBServer()
        {
            _sqliteCon = null;
        }

        public string DataSource
        {
            get { return DataSouce; }
            set
            {
                string str = value;
                str = str.Trim();
                str = str.Substring(str.Length - 2, 2);
                if (str != "db")
                {
                    MessageBox.Show("File type is not right!", "Warning");
                }
                else
                {
                    DataSouce = value;
                }
            }
        }

        public SQLiteConnection SqliteConnect
        {
            get { return _sqliteCon; }
        }

        public void ConnectedDbClose()
        {
            if (_sqliteCon != null)
            {
                try
                {
                    _sqliteCon.Close();
                    _sqliteCon.Dispose();
                    _sqliteCon = null;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        public void ConnectedDbOpen()
        {
            if (File.Exists(ModCommon.DBName))
            {
                try
                {
                    _sqliteCon = new SQLiteConnection(DataSouce);
                    _sqliteCon.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                MessageBox.Show(DataSouce + "  not Exist!", "warning");
            }
        }

        public int ExecNoneQuery(string sql)
        {
            int result = 0;
            try
            {
                ConnectedDbOpen();
                SqliteCom = new SQLiteCommand(sql, _sqliteCon);
                result = SqliteCom.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show(sql);
            }
            finally
            {
                ConnectedDbClose();
                SqliteCom = null;
            }
            return result;
        }

        public DataTable GetDataTable(string sql)
        {
            var dt = new DataTable();
            try
            {
                _sqliteCon = new SQLiteConnection(DataSouce);
                SqliteDa = new SQLiteDataAdapter(sql, _sqliteCon);
                SqliteDa.Fill(dt);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show(sql);
            }
            finally
            {
                ConnectedDbClose();
                SqliteDa.Dispose();
                SqliteDa = null;
            }
           return dt;
        }
    }
}